from flask import Flask, jsonify, abort, request
from flask_cors import CORS, cross_origin
import mysql.connector
from config import dbconfig

app = Flask(__name__)
cors = CORS(app)

@app.route("/products", methods=["delete"])
def deleteProduct():
    if not request.form:
        abort(400)

    product_id = request.form["product_id"]

    conn = mysql.connector.connect(**dbconfig.config)
    curs = conn.cursor()

    curs.execute(f"""
    delete from products where product_id = {product_id}
    """)

    rowcount = curs.rowcount
    if rowcount > 0:
        conn.commit()

    curs.close()
    conn.close()

    jsonrst = []
    jsonrst.append({
        'rowcount': rowcount
    })

    return jsonify(jsonrst)


@app.route("/products", methods=["put"])
def putProduct():
    if not request.form:
        abort(400)
    
    product_id = request.form["product_id"]
    name = request.form["name"]
    quantity_in_stock = request.form["quantity_in_stock"]
    unit_price = request.form["unit_price"]

    conn = mysql.connector.connect(**dbconfig.config)
    curs = conn.cursor()

    curs.execute(f"""
    update products set
        name = '{name}',
        quantity_in_stock = {quantity_in_stock},
        unit_price = {unit_price}
    where
        product_id = {product_id}
    """)

    rowcount = curs.rowcount

    conn.commit()
    curs.close()
    conn.close()

    jsonrst = []
    jsonrst.append({
        'rowcount': rowcount
    })

    return jsonify(jsonrst)

@app.route("/products", methods=["post"])
def postProduct():
    if not request.form or not request.form["name"] or not request.form["quantity_in_stock"] or not request.form["unit_price"]:
        abort(400)

    name = request.form["name"]
    quantity_in_stock = request.form["quantity_in_stock"]
    unit_price = float(request.form["unit_price"])

    conn = mysql.connector.connect(**dbconfig.config)
    curs = conn.cursor()

    curs.execute(f"""
    insert into products (
        name,
        quantity_in_stock,
        unit_price
    )
    values (
        '{name}',
        {quantity_in_stock},
        {unit_price}
    )
    """)
    lastRowId = curs.lastrowid
    
    conn.commit()
    curs.close()
    conn.close()

    jsonrst = []
    jsonrst.append({
        "lastrowid": lastRowId
    })

    return jsonify(jsonrst)

@app.route("/products/<string:name>", methods=["get"])
def productsByName(name):
    conn = mysql.connector.connect(**dbconfig.config)
    curs = conn.cursor()
    
    curs.execute(f"""
    select
        product_id,
        name,
        quantity_in_stock,
        unit_price
    from
        products
    where
        name regexp '{name}'
    """)

    rst = curs.fetchall()
    jsonrst = []
    for r in rst:
        jsonrst.append({
            "product_id": r[0],
            "name": r[1],
            "quantity_in_stock": r[2],
            "unit_price": r[3]
        })

    curs.close()
    conn.close()

    return jsonify(jsonrst)

@app.route("/products/<int:id>", methods=["get"])
def producstById(id):
    conn = mysql.connector.connect(**dbconfig.config)
    curs = conn.cursor()
    
    curs.execute(f"""
    select
        product_id,
        name,
        quantity_in_stock,
        unit_price
    from
        products
    where
        product_id = {id}
    """)

    rst = curs.fetchall()
    jsonrst = []
    for r in rst:
        jsonrst.append({
            "product_id": r[0],
            "name": r[1],
            "quantity_in_stock": r[2],
            "unit_price": r[3]
        })

    curs.close()
    conn.close()

    return jsonify(jsonrst)

@app.route("/products", methods=["get"])
def products():
    conn = mysql.connector.connect(**dbconfig.config)
    curs = conn.cursor()

    curs.execute("""
    select
        product_id,
        name,
        quantity_in_stock,
        unit_price
    from
        products
    """)

    rst = curs.fetchall()
    jsonrst = []
    for r in rst:
        jsonrst.append({
            "product_id": r[0],
            "name": r[1],
            "quantity_in_stock": r[2],
            "unit_price": r[3]
        })

    curs.close()
    conn.close()

    return jsonify(jsonrst)

if __name__ == "__main__":
    app.run(port=5000, debug=True)
